SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


alter  FUNCTION f_finallsubmamt (
  @ABvouyearmonth INT,
  @AEvouyearmonth INT,
  @filid int,
  @moneyid int,
  @binder INT,
  @check INT
) RETURNS TABLE
AS
RETURN (
  SELECT VK.subjectid AS SubID, ve.moneyid, VE.SubCode, vouyearmonth, 
    MDebit, MCredit, MDCDebit, MDCCredit
    FROM
  (
    SELECT moneyid, SubCode, vouyearmonth, sum(MDebit) AS MDebit, sum(MCredit) AS MCredit
      , sum(MDCDebit) AS MDCDebit, sum(MDCCredit) AS MDCCredit
      FROM
    (
      SELECT moneyid,substring(subcode, 1, (SELECT sc1len FROM f_finsublen())) AS SubCode,
        vouyearmonth, MDebit, MCredit, MDCDebit, MDCCredit FROM
      (
        SELECT * FROM f_finendsubmamt(@ABvouyearmonth, @AEvouyearmonth, @filid, @moneyid, @binder, @check)
      ) AS VC
      UNION ALL
      SELECT moneyid,SubString(subcode, 1, (SELECT sc2len FROM f_finsublen())) AS SubCode,
        vouyearmonth, MDebit, MCredit, MDCDebit, MDCCredit FROM
      (
        SELECT * FROM f_finendsubmamt(@ABvouyearmonth, @AEvouyearmonth, @filid, @moneyid, @binder, @check)
          WHERE Len(subcode) >= (SELECT sc2len FROM f_finsublen())
      ) AS VC
      UNION ALL
      SELECT moneyid,SubString(subcode, 1, (SELECT sc3len FROM f_finsublen())) AS SubCode,
        vouyearmonth, MDebit, MCredit, MDCDebit, MDCCredit FROM
      (
        SELECT * FROM f_finendsubmamt(@ABvouyearmonth, @AEvouyearmonth, @filid, @moneyid, @binder, @check)
          WHERE Len(subcode) >= (SELECT sc3len FROM f_finsublen())

      ) AS VC
      UNION ALL
      SELECT moneyid,SubString(subcode, 1, (SELECT sc4len FROM f_finsublen())) AS SubCode,
        vouyearmonth, MDebit, MCredit, MDCDebit, MDCCredit FROM
      (
        SELECT * FROM f_finendsubmamt(@ABvouyearmonth, @AEvouyearmonth, @filid, @moneyid, @binder, @check)
          WHERE Len(subcode) >= (SELECT sc4len FROM f_finsublen())

      ) AS VC
      UNION ALL
      SELECT moneyid,SubString(subcode, 1, (SELECT sc5len FROM f_finsublen())) AS SubCode,
         vouyearmonth, MDebit, MCredit, MDCDebit, MDCCredit FROM
      (
        SELECT * FROM f_finendsubmamt(@ABvouyearmonth, @AEvouyearmonth, @filid, @moneyid, @binder, @check)
          WHERE Len(subcode) >= (SELECT sc5len FROM f_finsublen())
      ) AS VC
      UNION ALL
      SELECT moneyid,SubString(subcode, 1, (SELECT sc6len FROM f_finsublen())) AS SubCode,
        vouyearmonth, MDebit, MCredit, MDCDebit, MDCCredit FROM
      (
        SELECT * FROM f_finendsubmamt(@ABvouyearmonth, @AEvouyearmonth, @filid, @moneyid, @binder, @check)
          WHERE Len(subcode) >= (SELECT sc6len FROM f_finsublen())
      ) AS VC
    ) AS VD GROUP BY moneyid,SubCode, vouyearmonth
  ) AS VE
  LEFT JOIN tfd_Subject VK ON VK.subCode = VE.SubCode
)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

